﻿using DBUtil.MetaData;
using DBUtil.Provider.MySql.MetaData;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.MySql
{
    public partial class MySqlManage : DBManage
    {
        #region 获取元数据信息
        /// <summary>
        /// mysql中的schema和database同义
        /// </summary>
        /// <returns></returns>
        public override List<Schema> ShowSchemas()
        {
            var sql = $"select * from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='{db.DataBase}'";
            var dt = db.SelectDataTable(sql);
            var res = new List<Schema>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                res.Add(new MySqlShema()
                {
                    Name = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                    DataBaseName = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                    DEFAULT_CHARACTER_SET_NAME = dt.Rows[i]["DEFAULT_CHARACTER_SET_NAME"].ToString(),
                    DEFAULT_COLLATION_NAME = dt.Rows[i]["DEFAULT_COLLATION_NAME"].ToString()
                });
            }
            return res;

        }

        /// <summary>
        /// 是否给表填充详细信息,默认不填充,即只有表名称/表说明/创建/修改时间
        /// </summary>
        /// <returns></returns>
        public override List<Table> ShowTables(string schemaName, bool isDetail = false)
        {
            var manage = db.Manage as MySqlManage;
            var sql = $"select * from information_schema.TABLES t where TABLE_SCHEMA ='{db.DataBase}' and TABLE_TYPE='BASE TABLE'";
            var dt = db.SelectDataTable(sql);
            var tables = new List<Table>();
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var row = dt.Rows[i];
                var table = loadTableModel(row);
                if (isDetail)
                {
                    FillTableDetail(table);
                }
                tables.Add(table);
            }
            return tables;
        }

        /// <summary>
        /// 给表填充详细元数据信息
        /// </summary>
        /// <param name="table">表</param>
        private Table FillTableDetail(MySqlTable table)
        {
            FillColumns(table);
            table.Indexes = showTableIndexes(table.Name, table.SchemaName);
            return table;
        }

        private List<Index> showTableIndexes(string tableName, string schemaName = null)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            List<Index> indexs = new List<Index>();
            var sql = $"show index from {objName.FullName};";
            var dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var index = new Index
                {
                    DataBaseName = objName.DataBaseName,
                    TableName = objName.Name,
                    SchemaName = objName.Name,
                    CreateTime = null,
                    IsClustered = dt.Rows[i]["Key_name"].ToString() == "PRIMARY",
                    Desc = dt.Rows[i]["Comment"].ToString().IfNullOrEmptyUse(dt.Rows[i]["Index_comment"].ToString()),
                    IndexType = dt.Rows[i]["Index_type"].ToString(),
                    IsNullAble = dt.Rows[i]["Null"].To<bool>(),
                    IsUnique = !dt.Rows[i]["Non_unique"].To<bool>(),
                    LastUpdate = null,
                    Name = dt.Rows[i]["Key_name"].ToString(),
                    ColumnNames = dt.Rows[i]["Column_name"].ToString()
                };
                indexs.Add(index);
            }
            var igroup = indexs.GroupBy(i => i.Name);
            if (igroup.Any(i => i.Count() > 1))
            {
                indexs = igroup.Select(i =>
                {
                    if (i.Count() > 1)
                    {
                        var one = i.FirstOrDefault();
                        one.ColumnNames = i.Select(i => i.ColumnNames).ToStringSeparated(",");
                    }
                    return i.FirstOrDefault();
                }).ToList();
            }
            return indexs;
        }

        private MySqlTable loadTableModel(DataRow row)
        {
            var table = new MySqlTable()
            {
                Name = row["TABLE_NAME"].To<string>(),
                DataBaseName = row["TABLE_SCHEMA"].To<string>(),
                CreateTime = null,
                LastUpdate = null,
                Desc = row["TABLE_COMMENT"].To<string>(),
                Engine = row["ENGINE"].To<string>(),
                RowCount = row["TABLE_ROWS"].To<long>(),
                SchemaName = row["TABLE_SCHEMA"].To<string>(),

                INDEX_LENGTH = row["INDEX_LENGTH"].To<long>(),
                AVG_ROW_LENGTH = row["AVG_ROW_LENGTH"].To<long>(),
                DATA_LENGTH = row["DATA_LENGTH"].To<long>(),
                DATA_FREE = row["DATA_FREE"].To<long>(),
                MAX_DATA_LENGTH = row["MAX_DATA_LENGTH"].To<long>(),

                AUTO_INCREMENT = row["AUTO_INCREMENT"].To<long?>(),
                TABLE_COLLATION = row["TABLE_COLLATION"].To<string>(),
                CREATE_OPTIONS = row["CREATE_OPTIONS"].To<string>(),
                ROW_FORMAT = row["ROW_FORMAT"].To<string>(),
            };
            if (row["CREATE_TIME"].ToString().IsNotNullOrEmptyOrWhiteSpace()) table.CreateTime = row["CREATE_TIME"].To<DateTime>();
            if (row["UPDATE_TIME"].ToString().IsNotNullOrEmptyOrWhiteSpace()) table.LastUpdate = row["UPDATE_TIME"].To<DateTime>();
            return table;
        }

        /// <summary>
        /// 返回指定表的详细元数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        public override Table ShowTableDetail(string tableName, string schemaName = null)
        {
            return FillTableInfo(tableName);
        }

        /// <summary>
        /// 给表填充元数据信息
        /// </summary>
        /// <param name="tableName">表名称</param>
        private Table FillTableInfo(string tableName)
        {
            var table = new MySqlTable() { Name = tableName };
            table = FillTableInfo(table);
            FillColumns(table);
            FillTriggers(table);
            FillConstraints(table);
            FillIndexes(table);
            return table;
        }

        /// <summary>
        /// 根据表名填充表说明/创建时间/更新时间/架构
        /// </summary>
        /// <param name="table"></param>
        private MySqlTable FillTableInfo(MySqlTable table)
        {
            if (table == null) return null;
            var sql = $"select * from information_schema.TABLES t where TABLE_SCHEMA ='{db.DataBase}' and TABLE_TYPE='BASE TABLE' and TABLE_NAME='{table.Name}'";
            var dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var row = dt.Rows[i];
                table = loadTableModel(row);
            }
            return table;
        }

        /// <summary>
        /// 根据表名填充列信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillColumns(Table table)
        {
            if (table == null) return;
            string sql = $@"select * from information_schema.COLUMNS T WHERE T.TABLE_SCHEMA='{db.DataBase}' and T.TABLE_NAME='{table.Name}'";
            DataTable dt = db.SelectDataTable(sql);
            List<Column> columns = new List<Column>();
            table.PrimaryKeyColumns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var column = new MySqlColumn();
                    column.Name = dt.Rows[i]["COLUMN_NAME"].ToString();
                    column.TableName = table.Name;
                    //列类型
                    column.TypeString = dt.Rows[i]["COLUMN_TYPE"].ToString();
                    column.DATA_TYPE = dt.Rows[i]["DATA_TYPE"].ToString();
                    column.ConvertType();
                    //TODO 设置类别
                    //列说明
                    column.Desc = dt.Rows[i]["COLUMN_COMMENT"].ToString();
                    //字符排序规则
                    column.CHARACTER_SET_NAME = dt.Rows[i]["CHARACTER_SET_NAME"].ToString();
                    column.COLLATION_NAME = dt.Rows[i]["COLLATION_NAME"].ToString();
                    column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].To<long?>();
                    column.CHARACTER_OCTET_LENGTH = dt.Rows[i]["CHARACTER_OCTET_LENGTH"].To<long?>();
                    column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].To<long?>();
                    column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].To<long?>();
                    column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].To<long?>();
                    if (dt.Columns.Contains("SRS_ID")) column.SRS_ID = dt.Rows[i]["SRS_ID"].To<int?>();
                    //是否可空
                    column.IsNullAble = dt.Rows[i]["IS_NULLABLE"].ToString() == "NO" ? false : true;
                    //自增
                    var extra = dt.Rows[i]["EXTRA"].ToString();
                    column.IsIdentity = extra == "auto_increment" ? true : false;
                    if (column.IsIdentity)
                    {
                        //先设置为1,1
                        column.IdentityStart = 1;
                        column.IdentityIncre = 1;
                    }
                    //虚列
                    if (extra.Contains("VIRTUAL GENERATED"))
                    {
                        column.IsComputed = true;
                        column.IsPersisted = false;
                        column.ComputedDefinition = dt.Rows[i]["GENERATION_EXPRESSION"].ToString();
                    }
                    else if (extra.Contains("STORED GENERATED"))
                    {
                        column.IsComputed = true;
                        column.IsPersisted = true;
                        column.ComputedDefinition = dt.Rows[i]["GENERATION_EXPRESSION"].ToString();
                    }

                    //默认值
                    var de = dt.Rows[i]["COLUMN_DEFAULT"].ToString();
                    column.HasDefault = string.IsNullOrEmpty(de) ? false : true;
                    if (column.HasDefault) column.Default = de;
                    columns.Add(column);
                    //主键 唯一
                    var columnkey = dt.Rows[i]["COLUMN_KEY"].ToString();
                    if (columnkey.Contains("PRI"))
                    {
                        table.PrimaryKeyColumns.Add(column);
                        column.IsUnique = true;
                        column.IsPrimaryKey = true;
                    }
                    if (columnkey.Contains("UNI"))
                    {
                        column.IsUnique = true;
                    }
                }
            }
            table.Columns = columns;
            if (table.PrimaryKeyColumns.Count > 0)
            {
                table.PrimaryKey = string.Join(",", table.PrimaryKeyColumns.Select(col => col.Name).ToList());
            };
        }

        /// <summary>
        /// 根据表名填充触发器信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillTriggers(Table table)
        {
            table.Triggers = ShowTableTriggers(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 根据表名填充约束信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillConstraints(Table table)
        {
            table.Constraints = ShowTableConstraints(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 根据表名称填充索引信息
        /// </summary>
        /// <param name="table"></param>
        private void FillIndexes(Table table)
        {
            table.Indexes = ShowTableIndexes(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 返回所有的视图(仅用户创建的视图)
        /// </summary>
        /// <returns></returns>
        public override List<View> ShowViews(string schemaName = null)
        {
            List<View> views = new List<View>();
            if (schemaName.IsNullOrEmptyOrWhiteSpace()) schemaName = db.DataBase;
            var sql = $"select * from information_schema.views where table_schema='{db.DealSqlInject(schemaName)}'";
            var dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var name = dt.Rows[i]["TABLE_NAME"].ToString();
                var schema = dt.Rows[i]["TABLE_SCHEMA"].ToString();
                var view_definition = dt.Rows[i]["TABLE_SCHEMA"].ToString();
                var view = new MySqlView()
                {
                    Name = name,
                    SchemaName = schema,
                    VIEW_DEFINITION = view_definition,
                    DEFINER = dt.Rows[i]["DEFINER"].ToString(),
                    SECURITY_TYPE = dt.Rows[i]["SECURITY_TYPE"].ToString(),
                    CHECK_OPTION = dt.Rows[i]["CHECK_OPTION"].ToString()
                };
                view.CreateSql = $"create view `{schema}`.`{name}` as\r\n{view_definition}";
                views.Add(view);
            }
            return views;
        }

        /// <summary>
        /// 返回所有的存储过程(仅用户自定义的)
        /// </summary>
        /// <returns></returns>
        public override List<Procedure> ShowProcedures(string schemaName = null)
        {
            if (schemaName.IsNullOrEmptyOrWhiteSpace()) schemaName = db.DataBase;
            List<Procedure> res = new List<Procedure>();
            string sql = $@"select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and ROUTINE_SCHEMA='{db.DealSqlInject(schemaName)}'";
            DataTable dt = db.SelectDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var proc = new MySqlProcedure();
                    proc.Name = dt.Rows[i]["ROUTINE_NAME"].ToString();
                    proc.CreateTime = DateTime.Parse(dt.Rows[i]["CREATED"].ToString());
                    var lastupdate = dt.Rows[i]["LAST_ALTERED"].ToString();
                    if (!string.IsNullOrWhiteSpace(lastupdate))
                    {
                        proc.LastUpdate = DateTime.Parse(lastupdate);
                    }
                    proc.CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString();
                    proc.SchemaName = db.DataBase;
                    sql = $"show create PROCEDURE {proc.Name}";
                    var tt = db.SelectDataTable(sql);
                    proc.CreateSql = tt.Rows[0]["Create Procedure"].ToString();
                    res.Add(proc);
                }
            }
            return res;
        }

        /// <summary>
        /// 返回指定表的所有约束(mysql中只能查到主键、检查、唯一约束)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName"></param>
        /// <returns></returns>
        public override List<DBUtil.MetaData.Constraint> ShowTableConstraints(string tableName, string schemaName)
        {
            var table = new MySql.MetaData.MySqlTable()
            {
                Name = tableName
            };
            FillColumns(table);
            List<DBUtil.MetaData.Constraint> constraints = new List<DBUtil.MetaData.Constraint>();
            string sql = $"select * from information_schema.TABLE_CONSTRAINTS where table_schema='{db.DataBase}' and TABLE_NAME='{tableName}'";
            var dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var constraint = new MySqlConstraint();
                constraint.TableName = tableName;
                constraint.Name = dt.Rows[i]["CONSTRAINT_NAME"].ToString();
                constraints.Add(constraint);
                var constraint_schema = dt.Rows[i]["CONSTRAINT_SCHEMA"].ToString();
                string type = (dt.Rows[i]["constraint_type"] ?? "").ToString();
                if (type.StartsWith("CHECK"))
                {
                    constraint.Type = EnumConstraintType.Check;
                    sql = $"select CHECK_CLAUSE from information_schema.CHECK_CONSTRAINTS where CONSTRAINT_NAME='{constraint.Name}' and CONSTRAINT_SCHEMA='{constraint_schema}'";
                    constraint.Content = db.SelectScalar<string>(sql);
                }
                else if (type.StartsWith("PRIMARY"))
                {
                    constraint.Type = EnumConstraintType.PrimaryKey;
                    constraint.Constraintkeys = table.PrimaryKey;
                }
                else if (type.StartsWith("UNIQUE"))
                {
                    constraint.Type = EnumConstraintType.Unique;
                    constraint.Constraintkeys = constraint.Name;
                }
            }
            return constraints;
        }
        /// <summary>
        /// 返回指定表所有索引
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Index> ShowTableIndexes(string tableName, string schemaName)
        {
            List<Index> indexs = new List<Index>();
            if (string.IsNullOrWhiteSpace(tableName)) return indexs;
            string sql = $"show INDEX from {tableName}";
            var dt = db.SelectDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                var lastindexname = string.Empty;
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    var index = new MySqlIndex();
                    index.TableName = tableName;
                    index.Name = dt.Rows[i]["Key_name"].ToString();
                    string colname = dt.Rows[i]["Column_name"].ToString();
                    if (lastindexname == index.Name)
                    {
                        indexs[indexs.Count - 1].ColumnNames += colname;
                        continue;
                    }
                    lastindexname = index.Name;
                    index.ColumnNames += "," + colname;
                    index.Desc = dt.Rows[i]["Index_comment"].ToString();
                    //认为主键是聚集索引,其他的为非聚集索引
                    if (index.Name == "PRIMARY")
                    {
                        index.IsClustered = true;
                    }
                    else
                    {
                        index.IsClustered = false;
                    }
                    index.IndexType = dt.Rows[i]["Index_type"].ToString();
                    indexs.Add(index);
                }
            }
            return indexs;
        }
        #endregion
    }
}
